﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using System.Configuration;

namespace DAO
{
    public class DataProvider
    {
        private string _error;
        public string Error
        {
            get { return _error; }
            set { _error = value; }
        }

        private string _sqlConnectionstring;
        public string SqlConnectionstring
        {
            get { return _sqlConnectionstring; }
            set { _sqlConnectionstring = value; }
        }

        private SqlConnection _connect;
        public SqlConnection Connect
        {
            get { return _connect; }
            set { _connect = value; }
        }

        public DataProvider()
        {
            
            this._sqlConnectionstring = ConfigurationManager.ConnectionStrings["DBBanHang"].ToString();
            this._connect = new SqlConnection(_sqlConnectionstring);
            this._error = "";
        }

        public bool MoKetNoi()
        {
            try
            {
                if (_connect == null)
                {
                    _connect = new SqlConnection(_sqlConnectionstring);
                }
                if (_connect.State == ConnectionState.Open)
                {
                    _connect.Close();
                }
                _connect.Open();
                return true;
            }
            catch (SqlException ex)
            {
                throw new Exception("Không thể kết nối tới csdl");
                _error = ex.Message;
                return false;
            }
        }

        public bool DongKetNoi()
        {
            try
            {
                if (_connect != null)
                {
                    if (_connect.State == ConnectionState.Open)
                        _connect.Close();

                }
                return true;
            }
            catch (SqlException ex)
            {
                _error = ex.Message;

            }
            return false;
        }
        //Su dung proc 
        public bool ExecuteNonQuery(string proc, SqlParameter[] sqlpram)
        {
            try
            {
                if (MoKetNoi())
                {

                    SqlCommand cmd = _connect.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    if (sqlpram != null)
                    {
                        cmd.Parameters.AddRange(sqlpram);
                    }
                    cmd.CommandText = proc;
                    _error = "";
                    cmd.ExecuteNonQuery();

                }
                return true;
            }
            catch (SqlException ex)
            {
                string[] chuoi = ex.Message.ToString().Split('.');
                throw new Exception(chuoi[0].ToString());

            }
        }

        /// <summary>
        /// Phuong thuc cho restore dữ liệu, truyền câu truy vấn
        /// </summary>
        /// <param name="proc"></param>
        /// <param name="sqlpram"></param>
        /// <returns></returns>
        public bool ExecuteNonQuery_Restore(string sql)
        {
            try
            {
                if (MoKetNoi())
                {
                    SqlCommand cmd = _connect.CreateCommand();
                    cmd.CommandType = CommandType.Text;

                    cmd.CommandText = sql;
                    _error = "";
                    cmd.ExecuteNonQuery();

                }
                return true;
            }
            catch (SqlException ex)
            {
                string[] chuoi = ex.Message.ToString().Split('.');
                throw new Exception(chuoi[0].ToString());

            }
        }
        public DataTable ExecuteQuery(string proc, SqlParameter[] param)
        {
            DataTable dt = null;
            try
            {
                if (MoKetNoi())
                {
                    SqlCommand cmd = _connect.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
                    cmd.CommandText = proc;
                    _error = "";
                    dt = new DataTable();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(dt);
                }
            }
            catch (SqlException ex)
            {
                string[] chuoi = ex.Message.ToString().Split('.');
                throw new Exception(chuoi[0].ToString());
                //_error = ex.Message;
            }
            finally
            {
                DongKetNoi();
            }
            return dt;
        }
    }
}
